#include "dbmanager.h"

DBManager * DBManager::instance = 0;

DBManager::DBManager(QObject *parent) :
    QObject(parent), initOk(false)
{
    db = QSqlDatabase::addDatabase("QSQLITE");

    #ifdef Q_OS_LINUX
        // NOTE: We have to store database file into user home folder in Linux
        QString path(QDir::home().path());
        path.append(QDir::separator()).append("bkmmem");
        path = QDir::toNativeSeparators(path);
        db.setDatabaseName(path);
    #else
        // NOTE: File exists in the application private folder, in Symbian Qt implementation
        db.setDatabaseName("my.db.sqlite");
    #endif
}

DBManager * DBManager::getGlobal()
{
    if (!instance)
        instance = new DBManager();
    return instance;
}

bool DBManager::initDB()
{
    if (initOk)
        return initOk;

    createOwnerTable();
    createVehicleTable();
    createUserTable();
    createWorkerTable();
    createSparePartTable();
    createRepairTable();
    createRepairSparePartTable();

    OwnerEntity owner1;
    owner1.setOwnerName("shan");
    owner1.setOwnerRegNo("SLD220030");
    owner1.setPhone1("077384893");
    owner1.setAddress1("34, Nuwara para");
    owner1.setCity1("Gampaha");
    owner1.setEmail("shan@atrenta.com");
    owner1.setDeleted(0);

    //insertOwner(&owner1);

    initOk = true;

    return initOk;
}

bool DBManager::createOwnerTable()
{
    openDB();

    bool ret = false;

    QString q ("CREATE TABLE IF NOT EXISTS Owner ( "
               "owner_id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "owner_name VARCHAR (125), "
               "owner_reg_no VARCHAR (64), "
               "phone1 VARCHAR (10), "
               "phone2 VARCHAR (10), "
               "address1 VARCHAR (255), "
               "city1 VARCHAR (64), "
               "address2 VARCHAR (255), "
               "city2 VARCHAR (64), "
               "email VARCHAR (128), "
               "fax VARCHAR (128), "
               "deleted INTEGER)");

    if (db.isOpen())
    {
        QSqlQuery query;
        ret = query.exec(q);
    }

    closeDB();

    return ret;
}

bool DBManager::createVehicleTable()
{
    openDB();

    bool ret = false;

    QString q ("CREATE TABLE IF NOT EXISTS Vehicle ( "
               "vehicle_id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "owner_id INTEGER, "
               "vehicle_reg_no VARCHAR (64), "
               "vehicle_eng_no VARCHAR (64), "
               "vehicle_chs_no VARCHAR (64), "
               "vehicle_model VARCHAR (64), "
               "vehicle_desc VARCHAR (255), "
               "deleted INTEGER)");

    if (db.isOpen())
    {
        QSqlQuery query;
        ret = query.exec(q);
    }

    closeDB();

    return ret;
}

bool DBManager::createUserTable()
{
    openDB();

    bool ret = false;

    QString q ( "CREATE TABLE IF NOT EXISTS User ( "
                "user_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "name VARCHAR (255), "
                "address VARCHAR (255), "
                "city VARCHAR (255), "
                "nic_no VARCHAR (32), "
                "phone VARCHAR (32), "
                "joined_date DATE, "
                "perm INTEGER, "
                "username VARCHAR (255), "
                "password VARCHAR (255), "
                "deleted INTEGER)");

    if (db.isOpen())
    {
        QSqlQuery query;
        ret = query.exec(q);
    }

    closeDB();

    return ret;

}

bool DBManager::createWorkerTable()
{
    openDB();

    bool ret = false;

    QString q ( "CREATE TABLE IF NOT EXISTS Worker ( "
                "worker_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "name VARCHAR (255), "
                "address VARCHAR (255), "
                "city VARCHAR (255), "
                "nic_no VARCHAR (32), "
                "phone VARCHAR (32), "
                "joined_date DATE, "
                "deleted INTEGER)");

    if (db.isOpen())
    {
        QSqlQuery query;
        ret = query.exec(q);
    }

    closeDB();

    return ret;
}

bool DBManager::createSparePartTable()
{
    openDB();

    bool ret = false;

    QString q ( "CREATE TABLE IF NOT EXISTS SparePart ( "
                "sp_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "sp_code VARCHAR (255), "
                "sp_name VARCHAR (255), "
                "unit_cost FLOAT, "
                "min_cost FLOAT, "
                "max_cost FLOAT, "
                "desc VARCHAR (255), "
                "deleted INTEGER)");

    if (db.isOpen())
    {
        QSqlQuery query;
        ret = query.exec(q);
    }

    closeDB();

    return ret;
}

bool DBManager::createRepairTable()
{
    openDB();

    bool ret = false;

    QString q ( "CREATE TABLE IF NOT EXISTS Repair ( "
                "repair_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "repair_code VARCHAR (255), "
                "repair_name VARCHAR (255), "
                "desc VARCHAR (255), "
                "deleted INTEGER)");

    if (db.isOpen())
    {
        QSqlQuery query;
        ret = query.exec(q);
    }

    closeDB();

    return ret;
}

bool DBManager::createRepairSparePartTable()
{
    openDB();

    bool ret = false;

    QString q ( "CREATE TABLE IF NOT EXISTS RepairSparePart ( "
                "rep_sp_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "repair_id INTEGER NOT NULL, "
                "sp_id INTEGER NOT NULL, "
                "deleted INTEGER)");

    if (db.isOpen())
    {
        QSqlQuery query;
        ret = query.exec(q);
    }

    closeDB();

    return ret;
}

void DBManager::insertOwner(OwnerEntity * owner)
{
    openDB();

    if (!owner)
        return;

    int newId = -1;
    bool ret = false;

    if (db.isOpen())
    {
        QString q = "INSERT INTO Owner VALUES (NULL, ";
        q += "'" + owner->getOwnerName() + "', ";
        q += "'" + owner->getOwnerRegNo() + "', ";
        q += "'" + owner->getPhone1() + "', ";
        q += "'" + owner->getPhone2() + "', ";
        q += "'" + owner->getAddress1() + "', ";
        q += "'" + owner->getCity1() + "', ";
        q += "'" + owner->getAddress2() + "', ";
        q += "'" + owner->getCity2() + "', ";
        q += "'" + owner->getEmail() + "', ";
        q += "'" + owner->getFax() + "', ";
        q += QString::number(owner->getDeleted()) + ")";

        QSqlQuery query;
        ret = query.exec(q);

        if (ret)
        {
            newId = query.lastInsertId().toInt();
        }
    }

    closeDB();
}

void DBManager::insertVehicle(VehicleEntity * vehicle)
{
    openDB();

    if (!vehicle)
        return;

    int newId = -1;
    bool ret = false;

    if (db.isOpen())
    {
        QString q = "INSERT INTO Vehicle VALUES (NULL, ";
        q += QString::number(vehicle->getOwnerId()) + ", ";
        q += "'" + vehicle->getVehicleRegNo() + "', ";
        q += "'" + vehicle->getVehicleEngNo() + "', ";
        q += "'" + vehicle->getVehicleChsNo() + "', ";
        q += "'" + vehicle->getVehicleModel() + "', ";
        q += "'" + vehicle->getVehicleDesc() + "', ";
        q += QString::number(vehicle->getDeleted()) + ")";

        QSqlQuery query;
        ret = query.exec(q);

        if (ret)
        {
            newId = query.lastInsertId().toInt();
        }
    }

    closeDB();
}

void DBManager::insertUser(UserEntity * user)
{
    openDB();

    if (!user)
        return;

    int newId = -1;
    bool ret = false;

    if (db.isOpen())
    {
        QString q = "INSERT INTO User VALUES (NULL, ";
        q += "'" + user->getName() + "', ";
        q += "'" + user->getAddress() + "', ";
        q += "'" + user->getCity() + "', ";
        q += "'" + user->getNicNo() + "', ";
        q += "'" + user->getPhone() + "', ";
        q += "'" + user->getJoinedDate() + "', ";
        q += QString::number(user->getPerm()) + ", ";
        q += "'" + user->getUsername() + "', ";
        q += "'" + user->getPassword() + "', ";
        q += QString::number(user->getDeleted()) + ")";

        QSqlQuery query;
        ret = query.exec(q);

        if (ret)
        {
            newId = query.lastInsertId().toInt();
        }
    }

    closeDB();
}

void DBManager::insertWorker(WorkerEntity * worker)
{
    openDB();

    if (!worker)
        return;

    int newId = -1;
    bool ret = false;

    if (db.isOpen())
    {
        QString q = "INSERT INTO Worker VALUES (NULL, ";
        q += "'" + worker->getName() + "', ";
        q += "'" + worker->getAddress() + "', ";
        q += "'" + worker->getCity() + "', ";
        q += "'" + worker->getNicNo() + "', ";
        q += "'" + worker->getPhone() + "', ";
        q += "'" + worker->getJoinedDate() + "', ";
        q += QString::number(worker->getDeleted()) + ")";

        QSqlQuery query;
        ret = query.exec(q);

        if (ret)
        {
            newId = query.lastInsertId().toInt();
        }
    }

    closeDB();
}

void DBManager::insertSparePart(SparePartEntity * sp)
{
    openDB();

    if (!sp)
        return;

    int newId = -1;
    bool ret = false;

    if (db.isOpen())
    {
        QString unitCost = "";
        QString minCost = "";
        QString maxCost = "";

        QString q = "INSERT INTO SparePart VALUES (NULL, ";
        q += "'" + sp->getSpCode() + "', ";
        q += "'" + sp->getSpName() + "', ";
        q += "" + unitCost.setNum(sp->getUnitCost()) + ", ";
        q += "" + minCost.setNum(sp->getMinCost()) + ", ";
        q += "" + maxCost.setNum(sp->getMaxCost()) + ", ";
        q += "'" + sp->getDesc() + "', ";
        q += QString::number(sp->getDeleted()) + ")";

        QSqlQuery query;
        ret = query.exec(q);

        if (ret)
        {
            newId = query.lastInsertId().toInt();
        }
    }

    closeDB();
}

void DBManager::insertRepair(RepairEntity * rep)
{
    openDB();

    if (!rep)
        return;

    int newId = -1;
    bool ret = false;

    if (db.isOpen())
    {
        QString q = "INSERT INTO Repair VALUES (NULL, ";
        q += "'" + rep->getRepairCode() + "', ";
        q += "'" + rep->getRepairName() + "', ";
        q += "'" + rep->getRepairDesc() + "', ";
        q += QString::number(rep->getDeleted()) + ")";

        QSqlQuery query;
        ret = query.exec(q);

        if (ret)
        {
            newId = query.lastInsertId().toInt();
        }

        // insert Repair + Spare Part
        QList<int> spList = rep->getSpList();

        for (int i=0; i < spList.size(); ++i)
        {
            int id = -1;
            ret = false;

            QString q = "INSERT INTO RepairSparePart VALUES (NULL, ";
            q += "" + QString::number(newId) + ", ";
            q += "" + QString::number(spList.at(i)) + ", ";
            q += QString::number(0) + ")";

            QSqlQuery query;
            ret = query.exec(q);

            if (ret)
            {
                id = query.lastInsertId().toInt();
            }
        }
    }

    closeDB();
}

void DBManager::allOwners(QSqlTableModel * model)
{
    model->setTable("Owner");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    model->select();
    model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("owner_name"));
    model->setHeaderData(2, Qt::Horizontal, QObject::tr("owner_reg_no"));
    model->setHeaderData(3, Qt::Horizontal, QObject::tr("phone1"));
    model->setHeaderData(4, Qt::Horizontal, QObject::tr("phone2"));
    model->setHeaderData(5, Qt::Horizontal, QObject::tr("address1"));
    model->setHeaderData(6, Qt::Horizontal, QObject::tr("city1"));
    model->setHeaderData(7, Qt::Horizontal, QObject::tr("address2"));
    model->setHeaderData(8, Qt::Horizontal, QObject::tr("city2"));
    model->setHeaderData(9, Qt::Horizontal, QObject::tr("email"));
    model->setHeaderData(10, Qt::Horizontal, QObject::tr("fax"));
    model->setHeaderData(11, Qt::Horizontal, QObject::tr("deleted"));
}

void DBManager::allSpareParts(QSqlTableModel * model)
{
    model->setTable("SparePart");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    model->select();
    model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("sp_code"));
    model->setHeaderData(2, Qt::Horizontal, QObject::tr("sp_name"));
    model->setHeaderData(3, Qt::Horizontal, QObject::tr("unit_cost"));
    model->setHeaderData(4, Qt::Horizontal, QObject::tr("min_cost"));
    model->setHeaderData(5, Qt::Horizontal, QObject::tr("max_cost"));
    model->setHeaderData(6, Qt::Horizontal, QObject::tr("desc"));
    model->setHeaderData(11, Qt::Horizontal, QObject::tr("deleted"));
}

bool DBManager::openDB()
{
    return db.open();
}

void DBManager::closeDB()
{
    db.close();
}

QSqlError DBManager::getError()
{
    return db.lastError();
}
